package com.hectopascal.goalance.repository;

import com.hectopascal.goalance.pojo.Performance;
import com.hectopascal.goalance.pojo.WorkContent;
import com.hectopascal.goalance.pojo.dto.WorkContentBack;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;
import java.util.Optional;

@Repository
public interface WorkContentRepository extends CrudRepository<WorkContent, Integer> {
    @Query(value = "SELECT wc.*, u.username, u.department_id " +
            "FROM work_content wc " +
            "LEFT JOIN user u ON wc.user_id = u.id " +
            " where wc.id = :id", nativeQuery = true)
    Optional<WorkContentBack> getWorkContentById(Integer id);

    @Query(value = "SELECT wc.*, u.username, u.department_id " +
            "FROM work_content wc " +
            "LEFT JOIN user u ON wc.user_id = u.id " +
            "WHERE (:userId IS NULL OR wc.user_id = :userId) " +
            "AND (:status IS NULL OR wc.status = :status) " +
            "AND (u.department_id = :departmentId)", nativeQuery = true)
    List<WorkContentBack> getAllWorkContent(@Param("userId") Integer userId, @Param("status") String status, @Param("departmentId") Integer departmentId);


    @Modifying
    @Transactional
    @Query(value = "UPDATE work_content SET "
    + "status = CASE WHEN :status IS NOT NULL THEN :status ELSE status END, "
    + "comment = CASE WHEN :comment IS NOT NULL THEN :comment ELSE comment END "
    + "WHERE id = :id", nativeQuery = true)
    int workContentApprove(Integer id, Integer status, String comment);

    @Query(value = """
        SELECT 
            0 AS id,
            COUNT(*) AS workload,
            ROUND(SUM(CASE WHEN status = 'approved' THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS approvalrate
        FROM work_content
        WHERE (user_id = :userId)
          AND (:month IS NULL OR DATE_FORMAT(created_at, '%Y-%m') = :month)
        """, nativeQuery = true)
    Optional<Performance> calculatePerformance(
            @Param("userId") Integer userId,
            @Param("month") String month
    );

    @Modifying
    @Transactional
    @Query(value = "UPDATE work_content SET attachment = :attachment WHERE id = :id", nativeQuery = true)
    int updateAttachment(@Param("attachment") String attachment, @Param("id") Integer id);
}
